
CREATE OR REPLACE FUNCTION dbo.updatecitytiles(paramid bigint, zoom integer, dt date, mtype bpchar)
  RETURNS integer AS
$BODY$
	DECLARE

	BEGIN 
		UPDATE dbo."CityTiles" SET "Updated"=true WHERE 
		"GUID_CITYPROCESS"=paramid AND "ZoomLevel"=zoom AND "MapType"=mtype;
		--date a definir
		RETURN 1;
	END; 
$BODY$
  LANGUAGE 'plpgsql' ;

drop type CityInfo cascade;

CREATE TYPE CityInfo AS (
GUID bigint,
NAME character varying,
X real,
Y real);

CREATE OR REPLACE FUNCTION dbo.getcity(prov character varying, dt date)
  RETURNS cityinfo AS
$BODY$
	DECLARE CI CityInfo;
	DECLARE gi integer;
	BEGIN 
		SELECT geonameid INTO gi from dbo."CityGeoNames" c
		WHERE c.country_code like 'FR' and NOT Exists (SELECT * FROM dbo."CityProcess" cr
		                  WHERE cr."GID"=c.geonameid and cr."Provider"=prov);

		INSERT INTO dbo."CityProcess" ("GID", "Provider", "CovDate")
			VALUES(gi,prov,dt);
            
		SELECT a."GUID",gname,longitude,latitude INTO CI from dbo."CityGeoNames" c, dbo."CityProcess" a
		WHERE a."GUID"=currval('dbo."CityProcess_GUID_seq"') AND a."GID"=c.geonameid;

		RETURN CI ;
	END; 
$BODY$
  LANGUAGE 'plpgsql';


drop type TilesInfo cascade;


CREATE TYPE TilesInfo AS (
GUID bigint,
TILESURL character varying(8000),
ZOOMLEVEL int,
MAPTYPE character varying(5),
PROVIDER character varying,
COVDATE date);

CREATE OR REPLACE FUNCTION dbo.gettilesurl()
  RETURNS tilesinfo AS
$BODY$
  DECLARE
	nb date;
	CI TilesInfo;	
  BEGIN
    
    SELECT INTO nb min("CovDate") FROM dbo."CityTiles" where "Updated"=false;
    Select b."GUID_CITYPROCESS",b."TilesUrl",b."ZoomLevel",b."MapType",a."Provider",b."CovDate" INTO CI from dbo."CityTiles" b, dbo."CityProcess" a
    where b."Updated"=false  and b."GUID_CITYPROCESS"=a."GUID" and b."CovDate"=nb;	
    
    RETURN CI;
  END;
$BODY$
  LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION dbo.getcopyrtilesurl()
  RETURNS tilesinfo AS
$BODY$
  DECLARE
	nb date;
	CI TilesInfo;	
  BEGIN
    
    SELECT INTO nb min("CovDate") FROM dbo."CopyrTiles" where "Updated"=false;
    Select b."GUID_CITYPROCESS",b."TilesUrl",b."ZoomLevel",b."MapType",a."Provider",b."CovDate" INTO CI from dbo."CopyrTiles" b, dbo."CityProcess" a
    where b."Updated"=false  and b."GUID_CITYPROCESS"=a."GUID" and b."CovDate"=nb;	
    RETURN CI;
  END;
$BODY$
  LANGUAGE 'plpgsql';
 

CREATE OR REPLACE FUNCTION dbo.updatecityprocess(paramid bigint, dt date)
  RETURNS integer AS
$BODY$
	DECLARE

	BEGIN 
		UPDATE dbo."CityProcess" SET "Updated"=1,"CovDate"= dt WHERE "GUID"=paramid;
		RETURN 1;
	END; 
$BODY$
  LANGUAGE 'plpgsql' ;

CREATE OR REPLACE FUNCTION dbo.writecitytiles(paramid bigint, surl character varying, zoom integer, maptype character varying, dt date)
  RETURNS integer AS
$BODY$
DECLARE
	BEGIN 
		INSERT INTO dbo."CityTiles"("GUID_CITYPROCESS", "TilesUrl", "ZoomLevel","MapType", "CovDate") VALUES (paramID, sUrl ,zoom,mapType, dt);

		RETURN 1;
	END; 
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION dbo.writeresults(paramid bigint, code integer, cvalue character varying, zoom integer, dt date, mtype bpchar)
  RETURNS integer AS
$BODY$
DECLARE
	BEGIN 
		INSERT INTO dbo."CityResults" ("GUID_CITYPROCESS","Covcode","CovValue","ZoomLevel","CovDate","MapType") 
		SELECT paramID, Code,CValue,zoom,dt,mtype;
		--Commit;
		RETURN 1;
	END; 
$BODY$
  LANGUAGE 'plpgsql' ;

CREATE OR REPLACE FUNCTION dbo.updatecopyrtiles(paramid bigint, zoom integer, dt date, mtype bpchar)
  RETURNS integer AS
$BODY$
	DECLARE

	BEGIN 
		UPDATE dbo."CopyrTiles" SET "Updated"=true WHERE 
		"GUID_CITYPROCESS"=paramid AND "ZoomLevel"=zoom AND "MapType"=mtype;
		--date a definir
		RETURN 1;
	END; 
$BODY$
  LANGUAGE 'plpgsql';
ALTER FUNCTION dbo.updatecopyrtiles(bigint, integer, date, bpchar) OWNER TO postgres;

CREATE OR REPLACE FUNCTION dbo.writecopyrtiles(paramid bigint, surl character varying, zoom integer, maptype character varying, dt date)
  RETURNS integer AS
$BODY$
DECLARE
	BEGIN 
		INSERT INTO dbo."CopyrTiles"("GUID_CITYPROCESS", "TilesUrl", "ZoomLevel","MapType", "CovDate") VALUES (paramID, sUrl ,zoom,mapType, dt);

		RETURN 1;
	END; 
$BODY$
  LANGUAGE 'plpgsql';
ALTER FUNCTION dbo.writecopyrtiles(bigint, character varying, integer, character varying, date) OWNER TO postgres;
